"""
    项目质量看板V2.0 软件缺陷查询SQL
"""

# 按照人员项目子系统统计新建Bug数
def construct_person_system_subsys_create_bug_statistics(
        _testers,
        _project_ids,
        _date_start_time,
        _date_end_time,
        _severity,
        _page_No,
        _page_size
):
    return f"""
select 
	if(bug.project>0, pjectB.id, pd.program)				as project_id,
	if(bug.project>0, pjectB.name, pject.name) 				as project_name,
-- 	if(pj.id is not null, pj.id, pd.id)						as subsys_id, 
	pj.id						                            as subsys_id, 
	if(pj.name is not null, pj.name, pd.name)				as subsys_name,
	iter.id                                                 as iteration_id,
	bld.id                                                  as version_id,
	iter.name                                               as iteration_name,
	bld.name                                                as version_name,
	usr.realname											as tester,
	count(distinct bug.id)   								as total
FROM
	zt_bug as bug 
LEFT JOIN
	zt_project as pj 
on 
	pj.id=bug.project 
LEFT JOIN
	zt_product as pd 
ON
	pd.id = bug.product
LEFT JOIN
	zt_user as usr  
on 
	usr.account = bug.openedBy
LEFT JOIN
	zt_project as pject 
on 
	pject.id=pd.program
LEFT JOIN
	zt_project as pjectB 
on 
	pjectB.id = SUBSTRING_INDEX(SUBSTRING_INDEX(pj.path,',',2),',',-1)
LEFT JOIN
    zt_project as iter 
ON
    iter.id = bug.execution
LEFT JOIN  
    zt_build as bld 
ON  
    bld.execution = iter.id 
WHERE
	bug.deleted = '0'
{
    f'''AND
            pject.id in  {_project_ids}''' if _project_ids and len(_project_ids) > 1 
    else f'''
        AND
            pject.id = {_project_ids[0]}
    ''' if _project_ids
    else
        ''
}
{
    f'''AND
            usr.realname in  {_testers}''' if _testers and len(_testers) > 1 
    else f'''
        AND
            usr.realname = "{_testers[0]}"
    ''' if _testers
    else 
        ''
}
{
    '''AND
	        bug.openedDate >= '%s' ''' % _date_start_time if _date_start_time else ''
}
{
    '''AND
	        bug.openedDate < '%s' ''' % _date_end_time if _date_end_time else ''
}
{
    f'''AND
            bug.severity in {_severity}''' if _severity and len(_severity) > 1
    else f'''
        And 
            bug.severity = {_severity[0]}
    ''' if _severity
    else 
        ''
}
GROUP BY
    pd.id, bug.openedBy 
ORDER BY 
    bug.openedBy, project_id

limit {_page_No * _page_size}, {_page_size}
"""


# 按照人员项目子系统统计新建Bug数
def construct_person_system_subsys_create_bug_all(
        _testers,
        _project_ids,
        _date_start_time,
        _date_end_time,
        _severity
):
    return f"""
    select count(num) as total from
(
    select 
        bug.openedBy as num
    FROM
        zt_bug as bug 
    LEFT JOIN
        zt_project as pj 
    on 
        pj.id=bug.project 
    LEFT JOIN
        zt_product as pd 
    ON
        pd.id = bug.product
    LEFT JOIN
        zt_user as usr  
    on 
        usr.account = bug.openedBy
    LEFT JOIN
        zt_project as pject 
    on 
        pject.id=pd.program
    LEFT JOIN
        zt_project as pjectB 
    on 
        pjectB.id = SUBSTRING_INDEX(SUBSTRING_INDEX(pj.path,',',2),',',-1)
    WHERE
        bug.deleted = '0'
    {
    f'''AND
                pject.id in  {_project_ids}''' if _project_ids and len(_project_ids) > 1
    else f'''
            AND
                pject.id = {_project_ids[0]}
        ''' if _project_ids
    else
    ''
    }
    {
    f'''AND
                usr.realname in  {_testers}''' if _testers and len(_testers) > 1
    else f'''
            AND
                usr.realname = "{_testers[0]}"
        ''' if _testers
    else
    ''
    }
    {
    '''AND
                bug.openedDate >= '%s' ''' % _date_start_time if _date_start_time else ''
    }
    {
    '''AND
                bug.openedDate < '%s' ''' % _date_end_time if _date_end_time else ''
    }
    {
    f'''AND
                bug.severity in {_severity}''' if _severity and len(_severity) > 1
    else f'''
            And 
                bug.severity = {_severity[0]}
        ''' if _severity
    else
    ''
    }
    GROUP BY
        pd.id, bug.openedBy 
) as temp_table
"""


# 按照人员项目子系统统计验证Bug数
def construct_person_system_subsys_verify_bug_statistics(
        _testers,
        _project_ids,
        _date_start_time,
        _date_end_time,
        _severity,
        _page_No,
        _page_size
):
    return f"""
  select
        pject.id            as project_id,
        pject.name          as project_name,
        pj.id               as subsys_id,
        pj.name             as subsys_name,
        iter.id             as iteration_id,
        bld.id              as version_id,
        iter.name           as iteration_name,
	    bld.name            as version_name,
        usr.realname        as tester,
        count(distinct bug.id) as total
    from
        zt_action as act
    left join
        zt_bug as bug
    on
        bug.id=act.objectID
    LEFT JOIN
        zt_project as pj 
    ON
        pj.id=bug.project
    LEFT JOIN
        zt_project as pject 
    ON
        pject.id=SUBSTRING_INDEX(SUBSTRING_INDEX(pj.path,",",2),",",-1) 
    left join
        zt_user as usr
    on
        usr.account=act.actor
    LEFT JOIN
        zt_project as iter 
    ON
        iter.id = bug.execution
    LEFT JOIN  
        zt_build as bld 
    ON  
        bld.execution = iter.id 
    where
		bug.deleted = '0'
	AND
        act.action='edited'
    and
        objectType='bug'
    and
        bug.status not in ('closed', 'resolved')
    and 
        pject.status != 'closed'
    and
        act.comment != ''
    and
        bug.openedBy = act.actor
{
    f'''AND
            pject.id in  {_project_ids}''' if _project_ids and len(_project_ids) > 1 
    else f'''
        AND
            pject.id = {_project_ids[0]}
    ''' if _project_ids
    else
        ''
}
{
    f'''AND
            usr.realname in  {_testers}''' if _testers and len(_testers) > 1 
    else f'''
        AND
            usr.realname = "{_testers[0]}"
    ''' if _testers
    else 
        ''
}
{
    '''AND
	        act.date >= '%s' ''' % _date_start_time if _date_start_time else ''
}
{
    '''AND
	        act.date < '%s' ''' % _date_end_time if _date_end_time else ''
}
{
    f'''AND
            bug.severity in {_severity}''' if _severity and len(_severity) > 1
    else f'''
        And 
            bug.severity = {_severity[0]}
    ''' if _severity
    else 
        ''
}
    group by
        act.actor, bug.product
    ORDER BY 
	    act.actor

limit {_page_No * _page_size}, {_page_size}
"""


def construct_person_system_subsys_verify_bug_all(
        _testers,
        _project_ids,
        _date_start_time,
        _date_end_time,
        _severity
):
    return f"""
select count(num) as total from
(
  select
        bug.openedBy as num
    from
        zt_action as act
    left join
        zt_bug as bug
    on
        bug.id=act.objectID
    LEFT JOIN
        zt_project as pj 
    ON
        pj.id=bug.project
    LEFT JOIN
        zt_project as pject 
    ON
        pject.id=SUBSTRING_INDEX(SUBSTRING_INDEX(pj.path,",",2),",",-1) 
    left join
        zt_user as usr
    on
        usr.account=act.actor
    where
		bug.deleted = '0'
	AND
        act.action='edited'
    and
        objectType='bug'
    and
        bug.status not in ('closed', 'resolved')
    and 
        pject.status != 'closed'
    and
        act.comment != ''
    and
        bug.openedBy = act.actor
{
    f'''AND
            pject.id in  {_project_ids}''' if _project_ids and len(_project_ids) > 1 
    else f'''
        AND
            pject.id = {_project_ids[0]}
    ''' if _project_ids
    else
        ''
}
{
    f'''AND
            usr.realname in  {_testers}''' if _testers and len(_testers) > 1 
    else f'''
        AND
            usr.realname = "{_testers[0]}"
    ''' if _testers
    else 
        ''
}
{
    '''AND
	        act.date >= '%s' ''' % _date_start_time if _date_start_time else ''
}
{
    '''AND
	        act.date < '%s' ''' % _date_end_time if _date_end_time else ''
}
{
    f'''AND
            bug.severity in {_severity}''' if _severity and len(_severity) > 1
    else f'''
        And 
            bug.severity = {_severity[0]}
    ''' if _severity
    else 
        ''
}
    group by
        act.actor, bug.product
) as temp_table
"""


def construct_person_system_subsys_closed_bug_statistics(
        _testers,
        _project_ids,
        _date_start_time,
        _date_end_time,
        _severity,
        _page_No,
        _page_size
):
    return f"""
SELECT
        pject.id            as project_id,
        pject.name          as project_name,
        pj.id               as subsys_id,
        pj.name             as subsys_name,
        iter.id             as iteration_id,
        bld.id              as version_id,
        iter.name           as iteration_name,
	    bld.name            as version_name,
        usr.realname        as tester,
        count(distinct bug.id)       as total
    FROM
        zt_bug as bug 
    LEFT JOIN
        zt_project as pj 
    ON
        pj.id = bug.project 
    LEFT JOIN
        zt_project as pject 
    ON
        pject.id=SUBSTRING_INDEX(SUBSTRING_INDEX(pj.path, ',', 2),',', -1)
    LEFT JOIN
        zt_user as usr 
    ON
        usr.account = bug.closedBy
    LEFT JOIN
        zt_project as iter 
    ON
        iter.id = bug.execution
    LEFT JOIN  
        zt_build as bld 
    ON  
        bld.execution = iter.id 
    WHERE
        bug.deleted='0'
    and 
        bug.status='closed'
--     and 
--         pject.status != 'closed'
{
    f'''AND
            pject.id in  {_project_ids}''' if _project_ids and len(_project_ids) > 1 
    else f'''
        AND
            pject.id = {_project_ids[0]}
    ''' if _project_ids
    else
        ''
}
{
    f'''AND
            usr.realname in  {_testers}''' if _testers and len(_testers) > 1 
    else f'''
        AND
            usr.realname = "{_testers[0]}"
    ''' if _testers
    else 
        ''
}
{
    '''AND
	        bug.closedDate >= '%s' ''' % _date_start_time if _date_start_time else ''
}
{
    '''AND
	        bug.closedDate < '%s' ''' % _date_end_time if _date_end_time else ''
}
{
    f'''AND
            bug.severity in {_severity}''' if _severity and len(_severity) > 1
    else f'''
        And 
            bug.severity = {_severity[0]}
    ''' if _severity
    else 
        ''
}
    GROUP BY
        bug.closedBy, pj.id
	ORDER BY
		bug.closedBy

limit {_page_No * _page_size}, {_page_size}
"""


def construct_person_system_subsys_closed_bug_all(
        _testers,
        _project_ids,
        _date_start_time,
        _date_end_time,
        _severity
):
    return f"""
    select count(num) as total from
(
    SELECT
            bug.closedBy as num
        FROM
            zt_bug as bug 
        LEFT JOIN
            zt_project as pj 
        ON
            pj.id = bug.project 
        LEFT JOIN
            zt_project as pject 
        ON
            pject.id=SUBSTRING_INDEX(SUBSTRING_INDEX(pj.path, ',', 2),',', -1)
        LEFT JOIN
            zt_user as usr 
        ON
            usr.account = bug.closedBy
        WHERE
            bug.deleted='0'
        and 
            bug.status='closed'
--         and 
--             pject.status != 'closed'
    {
    f'''AND
                pject.id in  {_project_ids}''' if _project_ids and len(_project_ids) > 1
    else f'''
            AND
                pject.id = {_project_ids[0]}
        ''' if _project_ids
    else
    ''
    }
    {
    f'''AND
                usr.realname in  {_testers}''' if _testers and len(_testers) > 1
    else f'''
            AND
                usr.realname = "{_testers[0]}"
        ''' if _testers
    else
    ''
    }
    {
    '''AND
                bug.closedDate >= '%s' ''' % _date_start_time if _date_start_time else ''
    }
    {
    '''AND
                bug.closedDate < '%s' ''' % _date_end_time if _date_end_time else ''
    }
    {
    f'''AND
                bug.severity in {_severity}''' if _severity and len(_severity) > 1
    else f'''
            And 
                bug.severity = {_severity[0]}
        ''' if _severity
    else
    ''
    }
        GROUP BY
            bug.closedBy
) as temp_table
"""


def construct_person_system_subsys_to_verify_bug_statistics(
        _testers,
        _project_ids,
        _date_start_time,
        _date_end_time,
        _severity,
        _page_No,
        _page_size
):
    return f"""
select 
	if(bug.project>0, pjectB.id, pd.program)				as project_id,
	if(bug.project>0, pjectB.name, pd.name) 				as project_name,
	pj.id					                                as subsys_id, 
	if(pj.name is not null, pj.name, pd.name)				as subsys_name,
	iter.id                                                 as iteration_id,
	bld.id                                                  as version_id,
    iter.name                                               as iteration_name,
	bld.name                                                as version_name,
	usr.realname											as tester,
	count(distinct bug.id)   										as total
FROM
	zt_bug as bug 
LEFT JOIN
	zt_project as pj 
on 
	pj.id=bug.project 
LEFT JOIN
	zt_product as pd 
ON
	pd.id = bug.product
LEFT JOIN
	zt_user as usr  
on 
	usr.account = bug.assignedTo
LEFT JOIN
	zt_project as pject 
on 
	pject.id=pd.program
LEFT JOIN
	zt_project as pjectB 
on 
	pjectB.id = SUBSTRING_INDEX(SUBSTRING_INDEX(pj.path,',',2),',',-1)
LEFT JOIN
    zt_project as iter 
ON
    iter.id = bug.execution
LEFT JOIN  
    zt_build as bld 
ON  
    bld.execution = iter.id 
WHERE
	bug.deleted = '0'
AND
	bug.`status` = 'resolved'
-- and 
-- 	bug.assignedTo = bug.openedBy
{
    f'''AND
            pject.id in  {_project_ids}''' if _project_ids and len(_project_ids) > 1 
    else f'''
        AND
            pject.id = {_project_ids[0]}
    ''' if _project_ids
    else
        ''
}
{
    f'''AND
            usr.realname in  {_testers}''' if _testers and len(_testers) > 1 
    else f'''
        AND
            usr.realname = "{_testers[0]}"
    ''' if _testers
    else 
        ''
}
{
    '''AND
	        bug.openedDate >= '%s' ''' % _date_start_time if _date_start_time else ''
}
{
    '''AND
	        bug.openedDate < '%s' ''' % _date_end_time if _date_end_time else ''
}
{
    f'''AND
            bug.severity in {_severity}''' if _severity and len(_severity) > 1
    else f'''
        And 
            bug.severity = {_severity[0]}
    ''' if _severity
    else 
        ''
}
GROUP BY
		pd.id, bug.assignedTo 
ORDER BY 
	bug.assignedTo
limit {_page_No * _page_size}, {_page_size}
"""


def construct_person_system_subsys_to_solved_bug_statistics(
        _testers,
        _project_ids,
        _date_start_time,
        _date_end_time,
        _severity,
        _page_No,
        _page_size
):
    return f"""
select 
	if(bug.project>0, pjectB.id, pd.program)				as project_id,
	if(bug.project>0, pjectB.name, pd.name) 				as project_name,
	pj.id						                            as subsys_id, 
	if(pj.name is not null, pj.name, pd.name)				as subsys_name,
	iter.id                                                 as iteration_id,
	bld.id                                                  as version_id,
    iter.name                                               as iteration_name,
	bld.name                                                as version_name,
	usr.realname											as tester,
	count(distinct bug.id)   										as total
FROM
	zt_bug as bug 
LEFT JOIN
	zt_project as pj 
on 
	pj.id=bug.project 
LEFT JOIN
	zt_product as pd 
ON
	pd.id = bug.product
LEFT JOIN
	zt_user as usr  
on 
	usr.account = bug.openedBy
LEFT JOIN
	zt_project as pject 
on 
	pject.id=pd.program
LEFT JOIN
	zt_project as pjectB 
on 
	pjectB.id = SUBSTRING_INDEX(SUBSTRING_INDEX(pj.path,',',2),',',-1)
LEFT JOIN
    zt_project as iter 
ON
    iter.id = bug.execution
LEFT JOIN  
    zt_build as bld 
ON  
    bld.execution = iter.id 
WHERE
	bug.deleted = '0'
AND
	bug.`status` = 'active'
{
    f'''AND
            pject.id in  {_project_ids}''' if _project_ids and len(_project_ids) > 1 
    else f'''
        AND
            pject.id = {_project_ids[0]}
    ''' if _project_ids
    else
        ''
}
{
    f'''AND
            usr.realname in  {_testers}''' if _testers and len(_testers) > 1 
    else f'''
        AND
            usr.realname = "{_testers[0]}"
    ''' if _testers
    else 
        ''
}
{
    '''AND
	        bug.openedDate >= '%s' ''' % _date_start_time if _date_start_time else ''
}
{
    '''AND
	        bug.openedDate < '%s' ''' % _date_end_time if _date_end_time else ''
}
{
    f'''AND
            bug.severity in {_severity}''' if _severity and len(_severity) > 1
    else f'''
        And 
            bug.severity = {_severity[0]}
    ''' if _severity
    else 
        ''
}
GROUP BY
		pd.id, bug.openedBy 
ORDER BY 
	bug.openedBy

limit {_page_No * _page_size}, {_page_size}
"""


# 查询 缺陷 统计页的 缺陷清单
def construct_sql_defect_statistics_detail(
        _project_id,
        _subsys_id,
        _iteration_id,
        _version_id,
        _creator_name,
        _assigner_name,
        _date_create_start,
        _date_create_end,
        _date_solve_start,
        _date_solve_end,
        _solve_resolution,
        _Bug_status,
        _Bug_severity,
        _project_status,
        _pageNo,
        _pageNum,
        _ids=None):
    return f"""
select
		pject.id as project_id,
		pj.id as subsys_id,
		pject.name as project_name,
		pj.name as subsys_name,
		iter.id as iteration_id,
		iter.name as iteration_name,
		bld.id as version_id,
		bld.name as version_name,
		bug.id as bug_id,
		bug.severity as severity,
		bug.title as title,
		usr.realname as creator,
		bug.openedDate as create_date,
		if(bug.status = 'closed', 'closed', usrB.realname) as assigner,
		bug.resolvedDate as sovled_date,
		bug.`status` as status,
		bug.resolution as resolution,
		pject.status as project_status
FROM
		zt_bug as bug 
LEFT JOIN
		zt_project as pj 
ON
		pj.id=bug.project
LEFT JOIN 
		zt_project as pject 
ON
		pject.id=SUBSTRING_INDEX(SUBSTRING_INDEX(pj.path,',',2),',',-1)
LEFT JOIN
		zt_user as usr 
ON
		usr.account=bug.openedBy
LEFT JOIN
		zt_user as usrB 
ON
		usrB.account=bug.assignedTo
left join  
            zt_project as iter
on 
            bug.execution = iter.id
left join  
            zt_build as bld 
on  
            bld.execution = iter.id
WHERE
		bug.deleted='0'
-- and
--         pject.status != 'closed'
{
'''AND
		usr.realname = '%s' # Bug创建人''' % _creator_name if _creator_name else ''
}
{
'''AND
		usrB.realname = '%s' # Bug指派人''' % _assigner_name if _assigner_name else ''
}
{
'''AND
		pject.id = %d # 项目id''' % _project_id if type(_project_id) is int and _project_id > 0 else 
            '''AND
                bug.project = %d # 项目id''' % _project_id if _project_id == 0
        else ''
}
{
'''AND
		pj.id = %d # 项目id''' % _subsys_id if type(_subsys_id) is int and _subsys_id > 0 else ''
}
{
'''AND
		iter.id = %d # 迭代id''' % _iteration_id if type(_iteration_id) is int and _iteration_id > 0 else ''
}
{
'''AND
		bld.id = %d # 迭代id''' % _version_id if type(_version_id) is int and _version_id > 0 else ''
}
{
f'''
		{"AND bug.openedDate > '%s'" % _date_create_start if _date_create_start else ''} # 创建的时间
		{"AND bug.openedDate <= '%s'" % _date_create_end if _date_create_end else ''}'''
}
{
f'''
		{"AND bug.resolvedDate > '%s'" % _date_solve_start if _date_solve_start else ''} # 解决Bug的时间
		{"AND bug.resolvedDate <= '%s'" % _date_solve_end if _date_solve_end else ''}'''
}
{
'''AND
		bug.`resolution` = '%s' # Bug解决状态''' % _solve_resolution if _solve_resolution else ''
}
{
'''AND
		bug.`status` = '%s' # Bug状态标签''' % _Bug_status if _Bug_status else ''
}

{
f'''AND
        bug.severity in {_Bug_severity}''' if _Bug_severity and len(_Bug_severity) > 1
else f'''
    And 
        bug.severity = {_Bug_severity[0]}
''' if _Bug_severity
else 
    ''
}
{
'''AND
		pject.`status` = '%s' # 项目状态''' % _project_status if _project_status else ''
}
{
'''AND
		bug.id = '%d' # id
	group by 
	    bug.id''' % _ids if _ids and len(_ids) == 1 else (
    ''  if not _ids 
        else 
f'''AND
		bug.id in {_ids} # id	
		''')
}
group by 
    bug.id
order by 
        bug.id
desc
limit {_pageNo * _pageNum}, {_pageNum}
"""


# 统计 缺陷 所有数据
def construct_sql_defect_statistics_all(
        _project_id,
        _subsys_id,
        _iteration_id,
        _version_id,
        _creator_name,
        _assigner_name,
        _date_create_start,
        _date_create_end,
        _date_solve_start,
        _date_solve_end,
        _solve_resolution,
        _Bug_status,
        _Bug_severity,
        _project_status,
):
    return f"""
select 
        count(distinct id) as total
from 
        (
        select
                bug.id
        FROM
                zt_bug as bug 
        LEFT JOIN
                zt_project as pj 
        ON
                pj.id=bug.project
        LEFT JOIN
                zt_project as pject 
        ON
                pject.id=SUBSTRING_INDEX(SUBSTRING_INDEX(pj.path,',',2),',',-1)
        LEFT JOIN
                zt_user as usr 
        ON
                usr.account=bug.openedBy
        LEFT JOIN
                zt_user as usrB 
        ON
                usrB.account=bug.assignedTo
        left join  
                zt_project as iter
        on 
                bug.execution = iter.id
        left join  
                zt_build as bld 
        on  
                bld.execution = iter.id
        WHERE
                bug.deleted='0'
--         and
--                 pject.status != 'closed'
        {
            '''AND
                usr.realname = '%s' # Bug创建人''' % _creator_name if _creator_name else ''
            }
        {
            '''AND
                usrB.realname = '%s' # Bug指派人''' % _assigner_name if _assigner_name else ''
            }
        {
            '''AND
                pject.id = %d # 项目id''' % _project_id if type(_project_id) is int and _project_id > 0 else 
            '''AND
                bug.project = %d # 项目id''' % _project_id if _project_id == 0
        else ''
            }
        {
            '''AND
                    pj.id = %d # 项目id''' % _subsys_id if type(_subsys_id) is int and _subsys_id > 0 else ''
            }
        {
            '''AND
                    pj.id = %d # 项目id''' % _subsys_id if type(_subsys_id) is int and _subsys_id > 0 else ''
            }
        {
            '''AND
                    iter.id = %d # 迭代id''' % _iteration_id if type(_iteration_id) is int and _iteration_id > 0 else ''
            }
        {
            '''AND
                    bld.id = %d # 迭代id''' % _version_id if type(_version_id) is int and _version_id > 0 else ''
            }
        {
            f'''
                {"AND bug.openedDate > '%s'" % _date_create_start if _date_create_start else ''} # 创建的时间
                {"AND bug.openedDate <= '%s'" % _date_create_end if _date_create_end else ''}'''
            }
        {
            f'''
                {"AND bug.resolvedDate > '%s'" % _date_solve_start if _date_solve_start else ''} # 解决Bug的时间
                {"AND bug.resolvedDate <= '%s'" % _date_solve_end if _date_solve_end else ''}'''
            }
        {
            '''AND
                    bug.`resolution` = '%s' # Bug解决状态''' % _solve_resolution if _solve_resolution else ''
            }
        {
            '''AND
                bug.`status` = '%s' # Bug状态标签''' % _Bug_status if _Bug_status else ''
            }
        {
            '''AND
                    bug.`severity` = '%s' # Bug严重等级''' % _Bug_severity if _Bug_severity else ''
            }
        {
            '''AND
                    pject.`status` = '%s' # 项目状态''' % _project_status if _project_status else ''
            }
        ) as new
"""


def construct_person_system_subsys_reopened_bug_statistics(
        _testers,
        _project_ids,
        _date_start_time,
        _date_end_time,
        _severity,
        _page_No,
        _page_size
):
    return f"""
select 
	if(bug.project>0, pjectB.id, pd.program)				as project_id,
	if(bug.project>0, pjectB.name, pd.name) 				as project_name,
    pj.id						                            as subsys_id, 
	if(pj.name is not null, pj.name, pd.name)				as subsys_name,
	iter.id                                                 as iteration_id,
	bld.id                                                  as version_id,
    iter.name                                               as iteration_name,
	bld.name                                                as version_name,
	usr.realname											as tester,
	count(distinct bug.id)   			                    as total
FROM
	zt_bug as bug 
LEFT JOIN
	zt_project as pj 
on 
	pj.id=bug.project 
LEFT JOIN
	zt_product as pd 
ON
	pd.id = bug.product
LEFT JOIN
	zt_user as usr  
on 
	usr.account = bug.openedBy
LEFT JOIN
	zt_project as pject 
on 
	pject.id=pd.program
LEFT JOIN
	zt_project as pjectB 
on 
	pjectB.id = SUBSTRING_INDEX(SUBSTRING_INDEX(pj.path,',',2),',',-1)
LEFT JOIN
    zt_project as iter 
ON
    iter.id = bug.execution
LEFT JOIN  
    zt_build as bld 
ON  
    bld.execution = iter.id 
WHERE
	bug.deleted = '0'
AND
    bug.activatedCount > 0
{
    f'''AND
            pject.id in  {_project_ids}''' if _project_ids and len(_project_ids) > 1 
    else f'''
        AND
            pject.id = {_project_ids[0]}
    ''' if _project_ids
    else
        ''
}
{
    f'''AND
            usr.realname in  {_testers}''' if _testers and len(_testers) > 1 
    else f'''
        AND
            usr.realname = "{_testers[0]}"
    ''' if _testers
    else 
        ''
}
{
    '''AND
	        bug.openedDate >= '%s' ''' % _date_start_time if _date_start_time else ''
}
{
    '''AND
	        bug.openedDate < '%s' ''' % _date_end_time if _date_end_time else ''
}
{
    f'''AND
            bug.severity in {_severity}''' if _severity and len(_severity) > 1
    else f'''
        And 
            bug.severity = {_severity[0]}
    ''' if _severity
    else 
        ''
}
GROUP BY
		pd.id, bug.openedBy
ORDER BY 
	bug.openedBy

limit {_page_No * _page_size}, {_page_size}
"""



def construct_person_system_subsys_reopened_bug_all(
        _testers,
        _project_ids,
        _date_start_time,
        _date_end_time,
        _severity
):
    return f"""
    select count(num) as total from
(
    select 
        count(1) as num
    FROM
        zt_bug as bug 
    LEFT JOIN
        zt_project as pj 
    on 
        pj.id=bug.project 
    LEFT JOIN
        zt_product as pd 
    ON
        pd.id = bug.product
    LEFT JOIN
        zt_user as usr  
    on 
        usr.account = bug.openedBy
    LEFT JOIN
        zt_project as pject 
    on 
        pject.id=pd.program
    LEFT JOIN
        zt_project as pjectB 
    on 
        pjectB.id = SUBSTRING_INDEX(SUBSTRING_INDEX(pj.path,',',2),',',-1)
    WHERE
        bug.deleted = '0'
    AND
        bug.activatedCount > 0
    {
        f'''AND
                pject.id in  {_project_ids}''' if _project_ids and len(_project_ids) > 1
        else f'''
            AND
                pject.id = {_project_ids[0]}
        ''' if _project_ids
        else
        ''
        }
    {
        f'''AND
                usr.realname in  {_testers}''' if _testers and len(_testers) > 1
        else f'''
            AND
                usr.realname = "{_testers[0]}"
        ''' if _testers
        else
        ''
        }
    {
        '''AND
                bug.openedDate >= '%s' ''' % _date_start_time if _date_start_time else ''
        }
    {
        '''AND
                bug.openedDate < '%s' ''' % _date_end_time if _date_end_time else ''
        }
    {
        f'''AND
                bug.severity in {_severity}''' if _severity and len(_severity) > 1
        else f'''
            And 
                bug.severity = {_severity[0]}
        ''' if _severity
        else
        ''
        }
    GROUP BY
		pd.id, bug.openedBy
) as temp_table
"""


Bug_all_project_name_list_sql = """
select 
		id, `name`, `status`
FROM
		zt_project
WHERE
		type in ('program', 'project')
AND
		project=0
AND
        parent=0
AND
		deleted='0'

;"""


# Bug创建人名单
Bug_creator_name_list_sql = """
SELECT
		usr.realname as name
FROM
		zentao.zt_bug as bug
LEFT JOIN
		zentao.zt_user as usr
ON
		usr.account=bug.openedBy
WHERE
		usr.deleted='0'
AND
        usr.realname != 'admin'
GROUP BY
		bug.openedBy
"""

# 查询所有的子系统
def construct_sql_Bug_subsys_by_project_search_sql(_project_id=None):
    return f"""
select 
        proj.id as id, 
        proj.name as name
from
        zt_project as proj   
where
        proj.type='project'
and
        proj.parent > 0
and
        proj.deleted = '0'
{
    f'and proj.parent={_project_id}' if _project_id 
    else ''
}
"""


# Bug指派人名单
Bug_assigner_name_list_sql = """
SELECT
		usr.realname as name
FROM
		zentao.zt_bug as bug
LEFT JOIN
		zentao.zt_user as usr
ON
		usr.account=bug.assignedTo
WHERE
		usr.deleted='0'
AND
        usr.realname != 'admin'
GROUP BY
		bug.assignedTo
;"""



